import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
# cursor.execute('''SELECT * FROM userdata''')
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print('查询所有数据')
all_data = cursor.fetchall()
print(all_data)
for row in all_data:
    print(row)
try:
    # 插入数据前先检查是否存在
    cursor.execute('''SELECT question FROM userdata WHERE question = ?''', 
                  ('sqlite与mysql有什么区别？',))
    
    if cursor.fetchone() is None:
        cursor.executemany('''INSERT INTO userdata (question, answer) 
                         VALUES (?, ?)''', 
                         [('sqlite与mysql有什么区别？', 
                          'sqlite使用的环境：低并发、取代本地文件存储、嵌入式物联网、内部数据库'),
                          ('then问题的关键是什么？', '问题的关键在于理解问题的本质和背景，以及问题的核心要素。'),
                          ('如何提高解决问题的能力？', '提高解决问题的能力需要不断学习和实践，积累经验，并保持好奇心和开放性思维。')
                          ])
        print("数据插入成功")
            # 查询所有数据
        cursor.execute('''SELECT * FROM userdata''')
        all_data = cursor.fetchall()
        for row in all_data:
            print(row)
    else:
        print("该问题已存在")
        conn.execute("UPDATE userdata SET answer = ? WHERE question = ?", 
                     (25000.00, 'sqlite与mysql有什么区别？'))
            # 查询所有数据
        cursor.execute('''SELECT * FROM userdata''')
        all_data = cursor.fetchall()
        for row in all_data:
            print(row)

    # 删除数据
    cursor.execute('''DELETE FROM userdata WHERE question = ?''', 
                  ('sqlite与mysql有什么区别？',))
    
    # 查询所有数据
    cursor.execute('''SELECT * FROM userdata WHERE question LIKE '%问题%' ''')
    print('查询问题包含“问题”的数据')
    all_data = cursor.fetchall()
    for row in all_data:
        print(row)

except sqlite3.Error as e:
    print(f"数据库错误: {e}")
finally:
    conn.commit()
    conn.close()
    pass

